package zy.dao.sell.day.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sell.day.DayDAO;
import zy.entity.sell.day.T_Sell_Day;
import zy.entity.sell.day.T_Sell_Weather;
import zy.util.CommonUtil;
import zy.util.StringUtil;
@Repository
public class DayDAOImpl extends BaseDaoImpl implements DayDAO{
	

	@Override
	public T_Sell_Day queryDay(Map<String, Object> paramMap) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT da_try,da_come,da_receive");
		sql.append(" FROM t_sell_day t");
		sql.append(" WHERE da_shop_code=:shop_code");
		sql.append(" AND da_date=:today");
		sql.append(" AND companyid=:companyid");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), paramMap, new BeanPropertyRowMapper<>(T_Sell_Day.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void come(Map<String, Object> paramMap) {
		StringBuffer sql = new StringBuffer("");
		Object da_id = paramMap.get("da_id");
		sql.append("INSERT INTO t_sell_receive(");
		sql.append("re_shop_code,re_date,re_type,companyid");
		sql.append(") VALUES (");
		sql.append(":shop_code,sysdate(),:re_type,:companyid");
		sql.append(")");
		paramMap.put("re_type", 0);//进店
		namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		sql.setLength(0);
		if(null != da_id && !"".equals(da_id)){
			sql.append(" UPDATE t_sell_day");
			sql.append(" SET da_come=da_come+1");
			sql.append(" WHERE da_id=:da_id");
			namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		}else{
			sql.append(" UPDATE t_sell_day");
			sql.append(" SET da_come=da_come+1");
			sql.append(" WHERE da_shop_code=:shop_code");
			sql.append(" AND da_date=:today");
			sql.append(" AND companyid=:companyid");
			namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		}
	}

	@Override
	public void receive(Map<String, Object> paramMap) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_receive(");
		sql.append("re_shop_code,re_date,re_em_code,re_type,companyid");
		sql.append(") VALUES (");
		sql.append(":shop_code,sysdate(),:em_code,:re_type,:companyid");
		sql.append(")");
		paramMap.put("re_type", 1);//接待
		namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		sql.setLength(0);
		Object da_id = paramMap.get("da_id");
		if(null != da_id && !"".equals(da_id)){
			sql.append(" UPDATE t_sell_day");
			sql.append(" SET da_receive=da_receive+1");
			sql.append(" WHERE da_id=:da_id");
			namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		}else{
			sql.append(" UPDATE t_sell_day");
			sql.append(" SET da_receive=da_receive+1");
			sql.append(" WHERE da_shop_code=:shop_code");
			sql.append(" AND da_date=:today");
			sql.append(" AND companyid=:companyid");
			namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		}
	}
	
	@Override
	public void comereceive(Map<String, Object> paramMap) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_receive(");
		sql.append("re_shop_code,re_date,re_em_code,companyid");
		sql.append(") VALUES (");
		sql.append(":shop_code,sysdate(),:em_code,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		sql.setLength(0);
		Object da_id = paramMap.get("da_id");
		if(null != da_id && !"".equals(da_id)){
			sql.append(" UPDATE t_sell_day");
			sql.append(" SET da_come=da_come+1");
			sql.append(" ,da_receive=da_receive+1");
			sql.append(" WHERE da_id=:da_id");
			namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		}else{
			sql.append(" UPDATE t_sell_day");
			sql.append(" SET da_come=da_come+1");
			sql.append(" ,da_receive=da_receive+1");
			sql.append(" WHERE da_shop_code=:shop_code");
			sql.append(" AND da_date=:today");
			sql.append(" AND companyid=:companyid");
			namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		}
	}

	@Override
	public void doTry(Map<String, Object> paramMap) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_try(");
		sql.append("tr_shop_code,tr_date,tr_em_code,companyid");
		sql.append(") VALUES (");
		sql.append(":shop_code,sysdate(),:em_code,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		sql.setLength(0);
		Object da_id = paramMap.get("da_id");
		if(null != da_id && !"".equals(da_id)){
			sql.append(" UPDATE t_sell_day");
			sql.append(" SET da_try=da_try+1");
			sql.append(" WHERE da_id=:da_id");
			namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		}else{
			sql.append(" UPDATE t_sell_day");
			sql.append(" SET da_try=da_try+1");
			sql.append(" WHERE da_shop_code=:shop_code");
			sql.append(" AND da_date=:today");
			sql.append(" AND companyid=:companyid");
			namedParameterJdbcTemplate.update(sql.toString(), paramMap);
		}
	}

	@Override
	public void saveDay(T_Sell_Day t_sell_day) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_sell_day (");
		sql.append(" da_try,da_come,da_receive,da_shop_code,da_date,companyid");
		sql.append(" ) VALUES(");
		sql.append(" :da_try,:da_come,:da_receive,:da_shop_code,:da_date,:companyid");
		sql.append(" )");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(t_sell_day),holder);
		int id = holder.getKey().intValue();
		t_sell_day.setDa_id(id);
	}

	@Override
	public String cityByCode(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT spi_city ");
		sql.append(" FROM t_base_shop_info");
		sql.append(" WHERE spi_shop_code=:shop_code");
		sql.append(" AND companyid=:companyid");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, String.class);
		} catch (Exception e) {
			return "";
		}
	}

	@Override
	public T_Sell_Weather weather(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT t.we_we_id,t.we_max_tmp,t.we_min_tmp,c.we_name,c.we_icon,t.we_wind");
		sql.append(" FROM t_sell_weather t");
		sql.append(" JOIN common_weather c");
		sql.append(" ON c.we_id=t.we_we_id");
		sql.append(" WHERE we_shop_code=:shop_code");
		sql.append(" AND we_date=:today");
		sql.append(" AND companyid=:companyid");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sell_Weather.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public T_Sell_Weather sysWeather(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT t.we_max_tmp,t.we_min_tmp,c.we_name,c.we_icon,t.we_wind,t.we_we_id");
		sql.append(" FROM t_sys_weather t");
		sql.append(" JOIN common_weather c");
		sql.append(" ON c.we_id=t.we_we_id");
		sql.append(" WHERE we_city=:city");
		sql.append(" AND we_date=:today");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sell_Weather.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Sell_Weather tomorrow(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT t.we_tomorrow,c.we_name");
		sql.append(" FROM t_sys_weather t");
		sql.append(" JOIN common_weather c");
		sql.append(" ON c.we_id=t.we_we_id");
		sql.append(" WHERE we_city=:city");
		sql.append(" AND we_date=:yestoday");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, new BeanPropertyRowMapper<>(T_Sell_Weather.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void saveSysWeather(T_Sell_Weather weather) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_sys_weather(");
		sql.append(" we_max_tmp,we_min_tmp,we_wind,we_we_id,we_date,we_city,we_tomorrow");
		sql.append(" ) VALUES (");
		sql.append(" :we_max_tmp,:we_min_tmp,:we_wind,:we_we_id,:we_date,:we_city,:we_tomorrow");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(weather));
	}

	@Override
	public void saveWeather(T_Sell_Weather weather) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_sell_weather(");
		sql.append(" we_max_tmp,we_min_tmp,we_wind,we_we_id,we_date,we_shop_code,companyid");
		sql.append(" ) VALUES (");
		sql.append(" :we_max_tmp,:we_min_tmp,:we_wind,:we_we_id,:we_date,:we_shop_code,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(weather));
	}
	
	@Override
	public Integer countreceive(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM t_sell_receive");
		sql.append(" WHERE 1=1");
		sql.append(" AND DATE_FORMAT(re_date,'%Y-%m-%d') = :today");
		sql.append(" AND re_shop_code = :shop_code");
		sql.append(" AND re_em_code = :em_code");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(),params, Integer.class);
	}

	@Override
	public List<T_Sell_Day> listByDate(Map<String, Object> paramMap) {
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		Object sh_shop_code = paramMap.get("sh_shop_code");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DAY(da_date) da_date,SUM(da_come) da_come,SUM(da_receive) da_receive,SUM(da_try) da_try");
		sql.append(" FROM t_sell_day t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.da_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(StringUtil.isNotEmpty(sh_shop_code)){
			paramMap.put("shop_codes", StringUtil.parseList(StringUtil.trimString(sh_shop_code)));
			sql.append(" AND sp_code IN (:shop_codes)");
		}
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
		}else{//自营、加盟、合伙
			sql.append(" AND sp_code=:shop_code");
		}
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(da_date,:date)>0");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY DAY(da_date)");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_Day.class));
	}

	@Override
	public List<T_Sell_Day> listByShop(Map<String, Object> paramMap) {
		String shop_type = StringUtil.trimString(paramMap.get(CommonUtil.SHOP_TYPE));
		Object begindate = paramMap.get("begindate");
		String jmd = StringUtil.trimString(paramMap.get("jmd"));
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT da_shop_code,SUM(da_come) da_come,SUM(da_receive) da_receive,SUM(da_try) da_try");
		sql.append(" FROM t_sell_day t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.da_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if("1".equals(shop_type) || "2".equals(shop_type)){//总公司、分公司
			if("1".equals(jmd)){
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_upcode = :shop_code AND sp_shop_type = "+CommonUtil.FOUR);
			}else {
				sql.append(getShopSQL(shop_type, 1));
				sql.append(" WHERE 1 = 1");
			}
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1=1");
			sql.append(" AND sp_code=:shop_code");
		}
		
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND da_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY da_shop_code");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_Day.class));
	}

}
